package com.ly.base.jdbc;

import com.ly.base.jdbc.pojo.UserAccount;
import com.mysql.cj.jdbc.Driver;
import org.junit.jupiter.api.Test;


import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


/**
 * @Classname JDBC
 * @Description TODO
 * @Date 2020/11/1 14:48
 * @Author 冷心影翼
 */
public class JDBC {



	@Test
	public void getConnection() throws SQLException {
		Driver driver = new Driver();
		String url = "jdbc:mysql://localhost:3236/wechat?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";
		Properties properties = new Properties();
		properties.setProperty("user","LY");
		properties.setProperty("password","252198ly");
		Connection connect = driver.connect(url, properties);
		System.out.println(connect);
	}

	@Test
	public void getConnection2() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
		Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
		Driver driver = (Driver) aClass.newInstance();
		String url = "jdbc:mysql://localhost:3236/wechat?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";
		Properties properties = new Properties();
		properties.setProperty("user","LY");
		properties.setProperty("password","252198ly");
		Connection connect = driver.connect(url, properties);
		System.out.println(connect);
	}

	@Test
	public void getConnection3() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
		Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3236/wechat?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";
		Connection connection = DriverManager.getConnection(url, "LY", "252198ly");
		System.out.println(connection);
	}

	@Test
	public void getConnection4() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException {
		InputStream inputStream = ClassLoader.getSystemResourceAsStream("mysql.properties");
		Properties properties = new Properties();
		properties.load(inputStream);
		Class.forName(properties.getProperty("driver"));
		Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties);
		System.out.println(connection);
	}

	@Test
	public void preStatementInsert() throws SQLException {
		Connection conn = getConn();
		String sql = "insert into user_account values(?,?)";
		try {

			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setObject(1,"A");
			preparedStatement.setObject(2,3);
			preparedStatement.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(conn);
		}
	}

	@Test
	public void preStatementUpdate() throws SQLException {
		Connection conn = getConn();
		String sql = "update  user_account set open_id = ?,left_message_count= ? where open_id = ?";
		try {
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setObject(1,"A");
			preparedStatement.setObject(2,0);
			preparedStatement.setObject(3,"A");
			preparedStatement.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(conn);
		}
	}

	@Test
	public void preStatementDelete() throws SQLException {
		Connection conn = getConn();
		String sql = "delete from user_account where open_id = ?";
		try {
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setObject(1,"A");
			preparedStatement.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(conn);
		}
	}

	@Test
	public void preStatementQuery() throws SQLException {
		Connection conn = getConn();
		String sql = "select * from user_account";
		try {
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int columnCount = metaData.getColumnCount();

			while (resultSet.next()) {
				String open_id = resultSet.getString("open_id");
				int left_message_count = resultSet.getInt("left_message_count");
				UserAccount uc = new UserAccount(open_id,left_message_count);
				System.out.println(uc);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(conn);
		}
	}


	public static void main(String[] args) throws SQLException {
		Connection conn = getConn();
		conn.setAutoCommit(false);
		for(int i=0;i<8;i++) {
			Thread t = new Thread(()->{
				for(int j=0;j<5000;j++) {
					PreparedStatement preparedStatement = null;
					System.out.println(j);
					try {
						preparedStatement = conn.prepareStatement("insert  into t2(f1,f2,f3,f4) values ('a"+j+"','b"+j+"','c','d')");
						preparedStatement.execute();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			});
			t.start();
		}
		while (Thread.activeCount()>3) {
			Thread.yield();
		}
		conn.commit();
	}

	@Test
	public void transactional() {
		Connection conn = getConn();
		try {
			conn.setAutoCommit(false);
			String sql1 = "update  user_account set open_id = ?,left_message_count= ? where open_id = ? ";
			PreparedStatement preparedStatement = conn.prepareStatement(sql1);
			preparedStatement.setObject(1,"B");
			preparedStatement.setObject(2,3);
			preparedStatement.setObject(3,"A");
			preparedStatement.executeUpdate();
//			int i = 10/0;
			String sql2 = "update  user_account set open_id = ?,left_message_count= ? where open_id = ? ";
			PreparedStatement preparedStatement2 = conn.prepareStatement(sql2);
			preparedStatement2.setObject(1,"C");
			preparedStatement2.setObject(2,2);
			preparedStatement2.setObject(3,"B");
			preparedStatement2.executeUpdate();

			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	public static Connection getConn()  {
		try {
			InputStream inputStream = ClassLoader.getSystemResourceAsStream("mysql.properties");
			Properties properties = new Properties();
			properties.load(inputStream);
			Class.forName(properties.getProperty("driver"));
			return DriverManager.getConnection(properties.getProperty("url"), properties);
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public void close(Connection connection) {
		try{
			if(connection != null) {
				connection.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}
